#!/usr/bin/env python
# coding=UTF-8
import MySQLdb
import MySQLdb.cursors
from DBUtils.PooledDB import PooledDB
import logging
import time

import config

pool = PooledDB(MySQLdb, 20, 20, 0, 500, **{'host':config.DB_HOST, 'port':config.DB_PORT, 'user':config.DB_USER, 'passwd':config.DB_PWD, 'db': config.DB_NAME, 'charset':'utf8', 'cursorclass':MySQLdb.cursors.DictCursor})

class BaseDB(object):
    def __init__(self, conn=None, slave=False, bid=''):
        if conn:
            self.conn = conn
        else:
            self.conn = pool.connection()
        self.cur = self.conn.cursor()
        self.res = True

    def __del__(self):
        self.finish()
        
    def execute(self, sql, param=None):
        try:
            if 'select' not in sql.lower()[:7]:
                logging.info(sql)
            else:
                logging.info(sql)
                logging.debug(sql)
            if not param:
                return self.cur.execute(sql)
            else:
                return self.cur.execute(sql, param)
        except:
            self.res = False
            logging.error(u"DB error: %s" % sql, exc_info=True)
    
    def get(self, sql, param=None):
        self.execute(sql, param)
        return self.cur.fetchone()

    def query(self, sql, param=None):
        self.execute(sql, param)
        return self.cur.fetchall()

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()
        
    def finish(self):
        if self.res:
            self.conn.commit()
        else:
            self.conn.rollback()
        if self.cur:
            self.cur.close()
        if self.conn:
            self.conn.close()
      
class Game_DB(BaseDB):
    def get_conf(self, bid):
        sql = "select param, value from t_conf where bid='%s'" % bid
        return self.query(sql)
    
    def get_game_des(self):
        sql = "select * from t_game_des"
        return self.query(sql)
    
    def get_game(self,gid):
        sql = "select * from t_game WHERE gid = '%s'" % gid
        return self.get(sql)
    
    def get_game_count(self,gid):
        sql = "select count(1) as c from t_game_member where gid = '%s'" %gid
        return self.get(sql)
    
    def user_info(self, bid, uid):
        sql = "select * from t_user where bid='%s' and uid=%s" % (bid, uid)
        return self.get(sql)
    
    def update_user(self, bid, uid, head, name, sex):
        sql = "update t_user set head='%s',name='%s',sex=%s where bid='%s' and uid=%s" % (head, name, sex, bid, uid)
        return self.execute(sql)
    
    def get_wx_user(self, bid, openid):
        sql = "select * from t_user where bid='%s' and openid='%s'" % (bid, openid)
        return self.get(sql)
    
    def get_dev_user(self, bid, devid):
        sql = "select * from t_user where bid='%s' and devid='%s'" % (bid, devid)
        return self.get(sql)
    
    def add_user(self, bid, uid, pwd, openid, head, name, sex, cp, cv, card, pid, devid, mcard):
        sql = "insert into t_user(bid, uid, pwd, md5pwd, openid, head, name, sex, cp, cv, card, pid, devid, mcard)values('%s',%s,'%s',MD5('%s'),'%s','%s','%s',%s,'%s','%s',%s,%s,'%s',%s)" % (bid, uid, pwd, pwd, openid, head, name, sex, cp, cv, card, pid, devid, mcard)
        return self.execute(sql)
    
    def get_max_uid(self, bid):
        sql = "select max(uid) as uid from t_user where bid='%s'" % bid
        return self.get(sql) 
    
    def get_joined_room(self, bid, uid):
        sql = "select a.admin, a.gid, a.mid, b.* from t_room a,t_room_player b where a.bid='%s' and a.flag='y' and b.uid=%s and a.rid=b.rid limit 1" % (bid, uid)
        return self.get(sql) 
    
    def get_joined_rid(self, bid, uid):
        sql = "select a.admin, a.gid, a.mid, a.rid from t_room a,t_room_player b where a.bid='%s' and a.flag='y' and b.uid=%s and a.rid=b.rid and (a.mid>0 or a.gid>0) limit 1" % (bid, uid)
        return self.get(sql) 
    
    def get_room(self, rid, bid):
        sql = "select * from t_room where rid=%s and bid='%s'" % (rid, bid)
        return self.get(sql) 
    
    def get_free_seat(self, rid):
        sql = "select * from t_room_player where rid=%s and (uid is null or uid=0) limit 1" % rid
        return self.get(sql) 
    
    def join_room(self, rid, seat, uid, status, online, ip, gps_lat, gps_lon):
        sql = "update t_room_player set uid=%s,status='%s',online='%s',ip='%s',gps_lat='%s',gps_lon='%s' where rid=%s and `seat`=%s and (uid is null or uid=0) limit 1" % (uid, status, online, ip, gps_lat, gps_lon, rid, seat)
        return self.execute(sql)

    def return_room(self, rid, seat, uid, status, online, ip, gps_lat, gps_lon):
        sql = "update t_room_player set status='%s',online='%s',ip='%s',gps_lat='%s',gps_lon='%s' where rid=%s and `seat`=%s and uid=%s limit 1" % (status, online, ip, gps_lat, gps_lon, rid, seat, uid)
        return self.execute(sql)
    
    def quit_room(self, rid, uid):
        sql = "update t_room_player set uid=NULL,status=0,online='n',ip='',gps_lat='',gps_lon='' where rid=%s and uid=%s" % (rid, uid)
        return self.execute(sql)
    
    def quit_club(self, cid, uid):
        sql = "delete from t_club_member where cid=%s and uid=%s and role!=2" % (cid, uid)
        return self.execute(sql)

    def admin_quit_room(self, rid, uid):
        sql = "update t_room_player set status=0,online='n' where rid=%s and uid=%s" % (rid, uid)
        return self.execute(sql)
    
    def query_room_players(self, rid, bid):
        sql = "select a.uid,a.head,a.name,a.sex,b.ip,b.status,b.online,b.seat,b.gps_lat,b.gps_lon,b.score from t_user a, t_room_player b where b.rid=%s and a.uid=b.uid and a.bid='%s'" % (rid, bid)
        return self.query(sql)
    
    def dismiss_room(self, rid, bid, uid):
        sql = "update t_room set flag='n' where rid=%s and bid='%s' and admin=%s" % (rid, bid, uid)
        return self.execute(sql)
    
    def dismiss_club(self, cid, bid, uid):
        sql = "update t_club set flag='n' where cid=%s and bid='%s' and admin=%s" % (cid, bid, uid)
        return self.execute(sql)
    
    def room_player_cnt(self, rid, status=None):
        sql = "select count(1) as player from t_room_player where rid=%s" % rid
        if status:
            sql += " and status=%s" % status
        else:
            sql += " and uid>0"
        return self.get(sql)
    
    def add_room(self, bid, game_type, admin, all_round, card, player, gid=0, mid=0, mround=1, base_score=1, des=''):
        sql = "insert into t_room(gid, mid, bid, game_type, admin, all_round, card, player, mround, base_score, des)values('%s','%s','%s','%s',%s,%s,%s,%s,%s,%s,'%s')" % (gid, mid, bid, game_type, admin, all_round, card, player, mround, base_score, des)
        i = self.execute(sql)
        return (i, int(self.cur.lastrowid))
    
    def add_player(self, mid, gid, rid, uid, status, online, ip, gps_lat, gps_lon, seat, score=1000):
        sql = "insert into t_room_player(mid, gid, rid, uid, status, online, ip, gps_lat, gps_lon, seat, score)values(%s,%s,%s,%s,%s,'%s','%s','%s','%s',%s,%s)" % (mid, gid, rid, uid, status, online, ip, gps_lat, gps_lon, seat, score)
        return self.execute(sql)
    
    def add_game_member(self, gid, uid, ip):
        sql = "insert into t_game_member(gid, uid, ip)values(%s,%s,'%s')" % (gid, uid, ip)
        return self.execute(sql)
    
    def add_feedback(self, bid, uid, content):
        sql = "insert into t_feedback(bid,uid,content)values(%s,%s,%s)" % (bid, uid, content)
        return self.execute(sql)
    
    def add_club(self, bid, name, admin, head, province, city, des, tag, max_user):
        sql = "insert into t_club(bid,name,admin,head,province,city,des,tag,max_user)values('%s','%s',%s,'%s','%s','%s','%s','%s',%s)" % (bid, name, admin, head, province, city, des, tag, max_user)
        i = self.execute(sql)
        return (i, int(self.cur.lastrowid))
    
    def add_game(self, cid, name, game_type, player, all_round, all_player, game_des):
        sql = "insert into t_game(cid,name,game_type,player,all_round, all_player, game_des)values(%s,'%s','%s',%s,%s,%s,'%s')" % (cid, name, game_type, player, all_round, all_player, game_des)
        i = self.execute(sql)
        return (i, int(self.cur.lastrowid))
                
    def add_game_rule(self, gid, param, value):
        sql = "insert into t_game_rule(gid,param,value)values(%s,'%s','%s')" % (gid, param, value)
        return self.execute(sql)

    def myclub(self, bid, uid):
        sql = "select cid,name,admin,head,bg_pic,city,des,tag,max_user,join_user from t_club where bid='%s' and admin=%s and flag='y'" % (bid, uid)
        return self.query(sql)
    
    def joined_club(self, bid, uid):
        sql = "select a.cid,a.name,a.admin,a.head,a.bg_pic,a.city,a.des,a.tag,b.role,b.verify,a.max_user,a.join_user from t_club a, t_club_member b where a.bid='%s' and a.cid=b.cid and b.uid=%s and a.flag='y' and b.verify>0 and role<2" % (bid, uid)
        return self.query(sql)
    
    def get_club(self, cid):
        sql = "select bid,cid,name,admin,head,city,tag,max_user,flag,des,join_user from t_club where cid=%s" % cid
        return self.get(sql)

    def get_myrole(self, cid, uid):
        sql = "select * from t_club_member where cid=%s and uid=%s" % (cid, uid)
        return self.get(sql)
    
    def get_club_member_len(self, cid):
        sql = "select count(1) as number,count(if(online='y',1,null)) as online_user from t_club_member where cid=%s" % cid
        return self.get(sql)
    
    def query_club_by_tag(self, tag):
        sql = "select cid,name,admin,head,city,tag,max_user from t_club where find_in_set('%s', tag)" % tag
        return self.query(sql)
    
    def query_club_by_city(self, bid, uid, city, limit=50):
        sql = "select cid,name,admin,head,city,tag,max_user,join_user from t_club where bid='%s' and admin!=%s and city='%s' limit %s" % (bid, uid, city, limit)
        return self.query(sql)
    
    def query_club_by_name(self, bid, uid, name, limit):
        sql = "select cid,name,admin,head,city,tag,max_user,join_user from t_club where bid='%s' and name like '%%%s%%' limit %s" % (bid, name, limit)
        return self.query(sql)
    
    def query_hot_club(self, bid, uid):
        sql = "select cid,name,admin,head,city,tag,max_user,join_user from t_club where bid='%s' and admin!=%s order by join_user desc limit 20" % (bid, uid) 
        return self.query(sql)
    
    def set_pid(self, bid, uid, pid):
        sql = "update t_user set pid=%s where bid='%s' and uid=%s" % (pid, bid, uid)
        return self.execute(sql)

    def set_signature(self, bid, uid, signature):
        sql = "update t_user set signature='%s' where bid='%s' and uid=%s" % (signature, bid, uid)
        return self.execute(sql)
    
    def join_club(self, uid, cid, role, verify):
        sql = "insert into t_club_member(cid,uid,role,verify)values(%s,%s,%s,%s)" % (cid, uid, role, verify)
        return self.execute(sql)
    
    def update_join_user(self, bid, cid, number):
        sql = "update t_club set join_user=join_user+%s where bid='%s' and cid=%s" % (number, bid, cid)
        return self.execute(sql)
    
    def unverify_member(self, bid, cid):
        sql = "select a.uid,a.name,a.head,b.cid,b.verify from t_user a, t_club_member b where a.bid='%s' and a.uid=b.uid and b.cid=%s and b.verify<2 and role=0" % (bid, cid)
        return self.query(sql)
    
    def club_member(self, bid, cid):
        sql = "select a.uid,a.name,a.head,b.role from t_user a, t_club_member b where a.bid='%s' and a.uid=b.uid and b.cid=%s and b.verify>0" % (bid, cid)
        return self.query(sql)
    
    def set_club_member(self, cid, uid, role, status):
        sql = "update t_club_member set role=%s, verify=%s where cid=%s and uid=%s" % (role, status, cid, uid)
        return self.execute(sql)
    
    def verify_club_member(self, cid, uid, verify):
        sql = "update t_club_member set verify=%s where cid=%s and uid=%s" % (verify, cid, uid)
        return self.execute(sql)
    
    def update_club_col(self, cid, col, col_value):
        sql = "update t_club set %s='%s' where cid=%s" % (col, col_value, cid)
        return self.execute(sql)
    
    def club_games(self, cid, status):
        sql = "select gid,cid,name,game_type,player,all_player,all_round,status,game_des from t_game where cid=%s and status=%s" % (cid, status)
        return self.query(sql)
    
    def game_members(self, gid):
        sql = "select * from t_game_member where gid=%s" % gid
        return self.query(sql)
    
    def game_rule(self, gid):
        sql = "select * from t_game_rule where gid=%s" % gid
        return self.query(sql)
    
    def add_room_rule(self, rid, param, value):
        sql = "insert into t_room_rule(rid,param,value)values(%s,'%s','%s')" % (rid, param, value)
        return self.execute(sql)
    
    def room_rule(self, rid):
        sql = "select * from t_room_rule where rid=%s" % rid
        return self.query(sql)
    
    def get_joined_game(self, uid):
        sql = "select a.* from t_game a, t_game_member b where a.gid=b.gid and b.uid=%s and a.status!=2 limit 1" % uid
        return self.get(sql)
    
    def in_club(self, cid, uid):
        sql = "select * from t_club_member where cid=%s and uid=%s" % (cid, uid)
        return self.get(sql)

    def update_club_online(self, cid, uid, online):
        sql = "update t_club_member set online='%s' where cid=%s and uid=%s" % (online, cid, uid)
        return self.execute(sql)
    
    def query_user_stat(self, bid, uid):
        sql = "select game_type,all_round,win_round,score from t_user_stat where bid='%s' and uid=%s" % (bid, uid)
        return self.query(sql)
    
    def add_match(self, bid, uid, game_type, score, all_player, start_time, name, des, status=4):
        sql = "insert into t_match(bid, admin, game_type, score, all_player, start_time, name, des, status)values('%s',%s,'%s',%s,%s,'%s','%s','%s','%s')" % (bid, uid, game_type, score, all_player, start_time, name, des, status)
        i = self.execute(sql)
        return (i, int(self.cur.lastrowid))
    
    def join_match(self, bid, uid, mid, role=0, pid=0, verify=0):
        sql = "insert into t_match_member(mid, uid, role, pid, verify)values(%s,%s,%s,%s,%s)" % (mid, uid, role, pid, verify)
        return self.execute(sql)
    
    def quit_match(self, bid, uid, mid):
        sql = "delete from t_match_member where uid=%s and mid=%s" % (uid, mid)
        return self.execute(sql)
    
    def match_info(self, bid, mid):
        sql = "select * from t_match where bid='%s' and mid=%s" % (bid, mid)
        return self.get(sql)
    
    def deduct_mcard(self, bid, uid, mcard=1):
        sql = "update t_user set mcard=mcard-%s where bid='%s' and uid=%s" % (mcard, bid, uid)
        return self.execute(sql)
    
    def my_match(self, bid, uid):
        sql = "select mid,name,des,all_player,game_type,score,status,start_time from t_match where bid='%s' and admin=%s order by mid desc " % (bid, uid)
        return self.query(sql)
    
    def joined_match(self, bid, uid):
        sql = "select a.mid,a.name,a.des,a.all_player,a.game_type,a.score,a.status,a.start_time,b.role,b.verify from t_match a,t_match_member b where a.bid='%s' and a.mid=b.mid and b.uid=%s and b.role<2 order by a.status, a.mid desc " % (bid, uid)
        return self.query(sql)
    
    def match_member(self, bid, mid, uid, admin_flag=False):
        if admin_flag:
            sql = "select a.uid,a.name,a.head,b.role,b.verify,'y' as verify_permission from t_user a, t_match_member b where a.bid='%s' and a.uid=b.uid and b.mid=%s order by b.verify" % (bid, mid)
        else:
            sql = "select a.uid,a.name,a.head,b.role,b.verify,if(b.pid=%s,'y','n')as verify_permission from t_user a, t_match_member b where a.bid='%s' and a.uid=b.uid and b.mid=%s order by verify_permission desc, b.verify" % (uid, bid, mid)
        return self.query(sql)
    
    def set_match_manager(self, bid, uid, mid):
        sql = "update t_match_member set role=1,verify=1 where mid=%s and uid=%s" % (mid, uid)
        return self.execute(sql)

    def unset_match_manager(self, bid, uid, mid):
        sql = "update t_match_member set role=0,verify=1 where mid=%s and uid=%s" % (mid, uid)
        return self.execute(sql)
    
    def update_match_online(self, mid, uid, online):
        sql = "update t_match_member set online='%s' where mid=%s and uid=%s" % (online, mid, uid)
        return self.execute(sql)
    
    def match_member_cnt(self, mid):
        sql = "select count(1) as number,count(if(online='y',1,null)) as online_user from t_match_member where mid=%s and role<1 and verify=1" % mid
        return self.get(sql)
    
    def verify_match_member(self, mid, uid):
        sql = "update t_match_member set verify=1 where mid=%s and uid=%s" % (mid, uid)
        return self.execute(sql)
    
    def del_match_member(self, mid, uid):
        sql = "delete from t_match_member where mid=%s and uid=%s" % (mid, uid)
        return self.execute(sql)
    
    def update_match_status(self, mid, status):
        sql = "update t_match set status=%s where mid=%s" % (status, mid)
        return self.execute(sql)
    
    def match_player(self, bid, mid):
        sql = "select * from t_match_member where mid=%s and role=0 and verify=1" % mid
        return self.query(sql)
    
    def enrolled_match(self, bid, uid):
        sql = "select a.mid,a.name,a.des,a.all_player,a.game_type,a.score,a.status,b.role,b.verify from t_match a,t_match_member b where a.bid='%s' and a.mid=b.mid and b.uid=%s and b.role<2 and a.status<3 order by a.status limit 1 " % (bid, uid)
        return self.get(sql)
    
    def all_match(self, bid, uid):
        sql = "select mid,name,des,all_player,game_type,score,status,start_time from t_match where bid='%s' and admin!=%s and status=0 order by mid desc limit 50" % (bid, uid)
        return self.query(sql)
    
    def update_game_status(self, gid):
        sql = "update t_game set status=2 where gid=%s" % gid
        return self.execute(sql)
    
    def match_ing_room(self, mid, mround):
        sql = "select count(1) as num from t_room where mid=%s and mround=%s and flag='y' limit 1" %  (mid, mround)
        return self.get(sql)
    
    def match_keep_palyer(self, mid):
        sql = "select * from t_match_member where mid=%s and res in (1, 3) and role=0 and verify=1 order by score desc, endtime" % mid
        return self.query(sql)
    
    def pre_round_players(self, mid, mround):
        sql = "select a.* from t_match_member a, t_match_score b where b.mid=%s and b.mround=%s and a.mid=b.mid and a.uid=b.uid order by score desc, endtime" % (mid, mround)
        return self.query(sql)
    
    def match_palyer_in(self, mid, uid):
        sql = "update t_match_member set res=1 where mid=%s and uid=%s" % (mid, uid)
        return self.query(sql)
    
    def match_palyer_out(self, mid, uid):
        sql = "update t_match_member set res=0 where mid=%s and uid=%s" % (mid, uid)
        return self.query(sql)
    
    def update_match_round(self, mid, mround):
        sql = "update t_match set mround=%s where mid=%s" % (mround, mid)
        return self.execute(sql)
    
    def match_over(self, mid):
        sql = "update t_match set status=3 where mid=%s" % mid
        return self.execute(sql)
    
    def revive_apply(self, mid, uid, score=None):
        if score == None:
            sql = "update t_match_member set res=3 where mid=%s and uid=%s" % (mid, uid)
        else:
            sql = "update t_match_member set res=3, score=%s where mid=%s and uid=%s" % (score, mid, uid)
        return self.execute(sql)
    
    def update_score(self, mid, uid, score, endtime):
        sql = "update t_match_member set score=score+%s,endtime='%s' where mid=%s and uid=%s" % (score, endtime, mid, uid)
        return self.execute(sql)
    
    def add_match_log(self, mid, mround, rid, game_round, uid, score):
        sql = "insert into t_match_log(mid,mround,rid,game_round,uid,score)values(%s,%s,%s,%s,%s,%s)" % (mid, mround,rid,game_round,uid,score)
        return self.execute(sql)
    
    def match_out_member(self, bid, mid, uid, admin_flag=False):
        if admin_flag:
            sql = "select a.uid,a.name,a.head,b.res,'y' as vive_permission from t_user a, t_match_member b where a.bid='%s' and a.uid=b.uid and b.mid=%s and b.role=0 and res!=1 and verify=1 " % (bid, mid)
        else:
            sql = "select a.uid,a.name,a.head,b.res,if(b.pid=%s,'y','n')as vive_permission from t_user a, t_match_member b where a.bid='%s' and a.uid=b.uid and b.mid=%s and b.role=0 and res!=1 and verify=1 " % (uid, bid, mid)
        return self.query(sql)
    
    def match_member_score(self, mid, mround, uid):
        sql = "select * from t_match_score where mid=%s and uid=%s and mround=%s" % (mid, uid, mround)
        return self.get(sql)
    
    def match_membe_ranking(self, mid, mround, score, ctime):
        sql = "select count(1) as num from t_match_score where mid=%s and mround=%s and (score>%s or (score=%s and ctime<'%s'))" % (mid, mround, score, score, ctime)
        return self.get(sql)
    
    def add_match_score(self, mid, mround, uid, score):
        sql = "insert into t_match_score(mid, mround, uid, score)values(%s,%s,%s,%s)" % (mid, mround, uid, score)
        return self.execute(sql)
    
    def match_member_info(self, mid, uid):
        sql = "select * from t_match_member where mid=%s and uid=%s" % (mid, uid)
        return self.get(sql)
    
    def match_score_log(self, mid, mround, bid):
        sql = "select a.uid,a.name,a.head,b.score from t_user a, t_match_score b where b.mid=%s and b.mround=%s and a.uid=b.uid and a.bid='%s' order by score desc" % (mid, mround, bid)
        return self.query(sql)
    
    def mround_room(self, mid, mround):
        sql = "select count(1) as num from t_room where mid=%s and mround=%s" % (mid, mround)
        return self.get(sql)
    
    def room_log(self, bid, uid):
        sql = "select a.head,a.name, b.*, from t_user a, (select a.rid, a.ctime, b.uid, sum(b.score)as score from t_room a, t_room_log b where a.bid='%s' and a.rid=b.rid and b.uid=%s group by rid order by rid desc) as b where a.bid='%s' and a.uid=b.uid" % (bid, uid, bid)
        return self.query(sql)
    
    def room_log_detail(self, rid):
        sql = "select uid,game_round,score from t_room_log where rid=%s" % rid
        return self.query(sql)
    
    def user_auth(self, bid, uid):
        sql = "select * from t_user_auth where bid='%s' and uid=%s" % (bid, uid)
        return self.get(sql)
    
    def update_user_auth(self, bid, uid, id_no, name):
        sql = "update t_user_auth set id_no='%s',name='%s' where bid='%s' and uid=%" % (id_no, name, bid, uid)
        return self.execute(sql)
    
    def update_room_score(self, rid, uid, score):
        sql = "update t_room_player set score=score+%s where rid=%s and uid=%s" % (score, rid, uid)
        return self.execute(sql)
    
    def update_match_info(self, bid, mid, name, des, all_player, start_time):
        sql = "update t_match set"
        if name:
            sql += " name='%s'" % name
        if des:
            sql += ",des='%s'" % des
        if all_player:
            sql += ",all_player='%s'" % all_player
        if start_time:
            sql += ",start_time='%s'" % start_time
        sql += " where bid='%s' and mid=%s" % (bid, mid)
        return self.execute(sql)
    
    def unauth_match(self, bid, uid):
        sql = "select count(1) as num from t_match where bid='%s' and admin=%s and status=4" % (bid, uid)
        return self.get(sql)
    
    def match_last_member(self, bid, mid):
        sql = "select * from t_match_member where mid=%s and res=1 order by score limit 1" % mid
        return self.get(sql)
    
    def current_ranking(self, mid, score, endtime):
        sql = "select count(1) as num from t_match_member where mid=%s and (score>%s or (score=%s and endtime<'%s')) and res in (1, 3)" % (mid, score, score, endtime)
        return self.get(sql)
    
    def middle_score(self, mid, num):
        if num < 1:
            num = 1
        sql = "select * from t_match_member where mid=%s and res in (1, 3) order by score desc, endtime limit %s, 1" % (mid, num-1)
        return self.get(sql)
    
    def first_score(self, mid):
        sql = "select * from t_match_member where mid=%s and res in (1, 3) order by score desc, endtime limit 1 " % (mid)
        return self.get(sql)
    
    def get_connserver(self):
        sql = "select ip, port from t_connserver where ctime>now()-interval 2 minute order by client limit 1"
        return self.get(sql)
    
    def ongoing_match(self, bid, uid):
        sql = "select a.*,b.score as user_score, b.endtime from t_match a, t_match_member b where a.bid='%s' and a.mid=b.mid and b.uid=%s and a.status in (1, 2) limit 1" % (bid, uid)
        return self.get(sql)
    
    def deduct_point(self, bid, uid, point):
        sql = "update t_user set point=point-%s where bid='%s' and uid=%s" % (point, bid, uid)
        return self.execute(sql)
    
    